package Entity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import Control.CommonControls;
import Control.MySQLController;

public class Workshop_WorkshopEntity {
	//Attributes
	private int joinedQuantity, totalQuantity;
	private String workshopId;
	private Workshop_WorkshopCategory category = new Workshop_WorkshopCategory();
	private String workshopName;
	private String description;
	private String venue;
	private Date startDateTime;
	private Date endDateTime;
	private Date workshopDateTime;
	private int capacity;
	private Workshop_TrainerEntity trainer = new Workshop_TrainerEntity();
	private Workshop_WorkshopStatusCode statusCode = new Workshop_WorkshopStatusCode();
	
	//Getters and Setters
	public String getWorkshopId() {
		return workshopId;
	}
	public void setWorkshopId(String workshopId) {
		this.workshopId = workshopId;
	}
	public Workshop_WorkshopCategory getCategory(){
		return category;
	}
	public void setCategory(Workshop_WorkshopCategory category){
		this.category = category;
	}
	public String getWorkshopName() {
		return workshopName;
	}
	public void setWorkshopName(String workshopName) {
		this.workshopName = workshopName;
	}
	public String getDescription() {
		return description;
	}
	public void setCapacity(int capacity) {
		this.capacity = capacity;
	}
	public String getVenue() {
		return venue;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public Date getStartDateTime() {
		return startDateTime;
	}
	public void setStartDateTime(Date startDateTime) {
		this.startDateTime = startDateTime;
	}
	public Date getEndDateTime() {
		return endDateTime;
	}
	public void setEndDateTime(Date endDateTime) {
		this.endDateTime = endDateTime;
	}
	public Date getWorkshopDateTime(){
		return workshopDateTime;
	}
	public void setWorkshopDateTime(Date workshopDateTime) {
		this.workshopDateTime = workshopDateTime;
	}
	public int getCapacity() {
		return capacity;
	}
	public void setVenue(String venue) {
		this.venue = venue;
	}
	public Workshop_TrainerEntity getTrainer() {
		return trainer;
	}
	public void setTrainer(Workshop_TrainerEntity trainer) {
		this.trainer = trainer;
	}
	public Workshop_WorkshopStatusCode getStatusCode() {
		return statusCode;
	}
	public void setStatusCode(Workshop_WorkshopStatusCode statusCode) {
		this.statusCode = statusCode;
	}
	public int getJoinedQuantity() {
		return joinedQuantity;
	}
	public void setJoinedQuantity(int joinedQuantity) {
		this.joinedQuantity = joinedQuantity;
	}
	public int getTotalQuantity() {
		return totalQuantity;
	}
	public void setTotalQuantity(int totalQuantity) {
		this.totalQuantity = totalQuantity;
	}
	
	//Constructors
	public Workshop_WorkshopEntity(){}
	
	public Workshop_WorkshopEntity(String workshopId){
		this.workshopId = workshopId;
	}
	
	public Workshop_WorkshopEntity(String workshopId, String category, String workshopName, String description, String venue, Date startDateTime, Date endDateTime, Date workshopDateTime, int capacity, String trainerId, String statusCode){
		this.workshopId = workshopId;
		this.category = new Workshop_WorkshopCategory(category);
		this.workshopName = workshopName;
		this.description = description;
		this.venue = venue;
		this.startDateTime = startDateTime;
		this.endDateTime = endDateTime;
		this.workshopDateTime = workshopDateTime;
		this.capacity = capacity;
		this.trainer = new Workshop_TrainerEntity(trainerId);
		this.statusCode = new Workshop_WorkshopStatusCode(statusCode);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public boolean createWorkshop(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String sql = "INSERT INTO ooadpj.workshop(workshop_id, category_code, name, description, venue, start_date_time, end_date_time, workshop_date_time, max_cap, trainer_id, workshop_status_code) ";
		sql += "VALUES ('" + workshopId + "','" + category.getCategoryCode() + "','" + workshopName + "','" + description + "','" + venue + "','" + sdf.format(startDateTime) + "','" + sdf.format(endDateTime) + "','" + sdf.format(workshopDateTime) + "','" + capacity + "','" + trainer.getTrainerId() + "','" + statusCode.getStatusCode() + "')";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public static ArrayList<Workshop_WorkshopEntity> retrieveAllWorkshops(String sort, String order, boolean search, boolean searchCategory, Workshop_WorkshopCategory category, boolean searchWorkshopName, String name, Date startDate){
		ArrayList<Workshop_WorkshopEntity> workshopList = new ArrayList<Workshop_WorkshopEntity>();
		
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String sql = "SELECT * FROM ooadpj.workshop ";
		sql += "INNER JOIN ooadpj.category ON workshop.category_code = category.category_code ";
		sql += "INNER JOIN ooadpj.trainer ON workshop.trainer_id = trainer.trainer_id ";
		sql += "INNER JOIN ooadpj.workshop_status ON workshop.workshop_status_code = workshop_status.workshop_status_code ";
		if(search){
			sql += "WHERE ";
			
			if(searchCategory){
				sql += "workshop.category_code = '" + category.getCategoryCode() + "' AND ";
			}
			if(searchWorkshopName){
				sql += "workshop.name LIKE '%" + name + "%' AND "; 
			}
			sql += "start_date_time >= '" + sdf.format(startDate) + "' ";
		}
		sql += "ORDER BY workshop.workshop_status_code, " + sort + " " + order;
		rs = db.readRequest(sql);
		try{
			while(rs.next()){
				Workshop_WorkshopEntity workshop = new Workshop_WorkshopEntity();
				workshop.setWorkshopId(rs.getString("workshop_id"));
				workshop.category.setCategoryCode(rs.getString("category_code"));
				workshop.category.setDescription(rs.getString("category.description"));
				workshop.setWorkshopName(rs.getString("workshop.name"));
				workshop.setDescription(rs.getString("workshop.description"));
				workshop.setVenue(rs.getString("venue"));
				workshop.setStartDateTime(rs.getTimestamp("start_date_time"));
				workshop.setEndDateTime(rs.getTimestamp("end_date_time"));
				workshop.setWorkshopDateTime(rs.getTimestamp("workshop_date_time"));
				workshop.setCapacity(rs.getInt("max_cap"));
				workshop.trainer.setTrainerId(rs.getString("trainer_id"));
				workshop.trainer.setTrainerName(rs.getString("trainer.name"));
				workshop.trainer.setContactNo(rs.getInt("contact_no"));
				workshop.statusCode.setStatusCode(rs.getString("workshop_status_code"));
				workshop.statusCode.setDescription(rs.getString("workshop_status.description"));
				workshopList.add(workshop);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return workshopList;
	}
	
	public boolean retrieveAWorkshop(){
		boolean success = false;
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT * FROM ooadpj.workshop ";
		sql += "INNER JOIN ooadpj.category ON workshop.category_code = category.category_code ";
		sql += "INNER JOIN ooadpj.trainer ON workshop.trainer_id = trainer.trainer_id ";
		sql += "INNER JOIN ooadpj.workshop_status ON workshop.workshop_status_code = workshop_status.workshop_status_code ";
		sql += "WHERE workshop_id = '" + workshopId + "'";
		rs = db.readRequest(sql);
		try{
			while(rs.next()){
				category.setCategoryCode(rs.getString("category_code"));
				category.setDescription(rs.getString("category.description"));
				workshopName = rs.getString("workshop.name");
				description = rs.getString("workshop.description");
				venue = rs.getString("venue");
				startDateTime = rs.getTimestamp("start_date_time");
				endDateTime = rs.getTimestamp("end_date_time");
				workshopDateTime = rs.getTimestamp("workshop_date_time");
				capacity = rs.getInt("max_cap");
				trainer.setTrainerId(rs.getString("trainer_id"));
				trainer.setTrainerName(rs.getString("trainer.name"));
				trainer.setContactNo(rs.getInt("contact_no"));
				statusCode.setStatusCode(rs.getString("workshop_status_code"));
				statusCode.setDescription(rs.getString("workshop_status.description"));
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			db.terminate();	
		}
		return success;
	}
	
	public static ArrayList<String> retrieveWorkshopsStatusOBS(){
		ArrayList<String> workshopIds = new ArrayList<String>();
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT workshop_id FROM ooadpj.workshop WHERE workshop.workshop_status_code = 'OBS'";
		rs = db.readRequest(sql);
		try {
			while(rs.next()){
				workshopIds.add(rs.getString("workshop_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return workshopIds;
	}
	
	public static ArrayList<Workshop_WorkshopEntity> retrieveOBSWorkshopUnderCat(Workshop_WorkshopCategory category, int month){
		ArrayList<Workshop_WorkshopEntity> workshopList = new ArrayList<Workshop_WorkshopEntity>();
		
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT *, COUNT(nric) AS joinedquantity, (COUNT(nric) + max_cap) AS totalquantity, ((COUNT(nric)/(COUNT(nric) + max_cap))*100) AS percentage FROM ooadpj.workshop ";
		sql += "INNER JOIN ooadpj.category ON workshop.category_code = category.category_code ";
		sql += "INNER JOIN ooadpj.trainer ON workshop.trainer_id = trainer.trainer_id ";
		sql += "INNER JOIN ooadpj.workshop_status ON workshop.workshop_status_code = workshop_status.workshop_status_code ";
		sql += "INNER JOIN ooadpj.book_workshop ON workshop.workshop_id = book_workshop.workshop_id ";
		sql += "WHERE workshop.workshop_status_code = 'OBS' AND workshop.category_code = '" + category.getCategoryCode() + "' AND MONTH(end_date_time) = " + (month+1) + " ";
		sql += "GROUP BY workshop.workshop_id ";
		sql += "ORDER BY percentage DESC, workshop.name";
		rs = db.readRequest(sql);
		try {
			while(rs.next()){
				Workshop_WorkshopEntity workshop = new Workshop_WorkshopEntity();
				workshop.setWorkshopId(rs.getString("workshop_id"));
				workshop.category.setCategoryCode(rs.getString("category_code"));
				workshop.category.setDescription(rs.getString("category.description"));
				workshop.setWorkshopName(rs.getString("workshop.name"));
				workshop.setDescription(rs.getString("workshop.description"));
				workshop.setVenue(rs.getString("venue"));
				workshop.setStartDateTime(rs.getTimestamp("start_date_time"));
				workshop.setEndDateTime(rs.getTimestamp("end_date_time"));
				workshop.setWorkshopDateTime(rs.getTimestamp("workshop_date_time"));
				workshop.setCapacity(rs.getInt("max_cap"));
				workshop.trainer.setTrainerId(rs.getString("trainer_id"));
				workshop.trainer.setTrainerName(rs.getString("trainer.name"));
				workshop.trainer.setContactNo(rs.getInt("contact_no"));
				workshop.statusCode.setStatusCode(rs.getString("workshop_status_code"));
				workshop.statusCode.setDescription(rs.getString("workshop_status.description"));
				workshop.setJoinedQuantity(rs.getInt("joinedquantity"));
				workshop.setTotalQuantity(rs.getInt("totalquantity"));
				workshopList.add(workshop);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return workshopList;
	}
	
	public boolean updateWorkshop(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String sql = "UPDATE ooadpj.workshop SET category_code = '" + category.getCategoryCode() + "', name = '" + workshopName + "', description = '" + description + "', venue = '" + venue + "', start_date_time = '" + sdf.format(startDateTime) + "', end_date_time = '" + sdf.format(endDateTime) + "', workshop_date_time = '" + sdf.format(CommonControls.getCurrentDate()) + "', max_cap = '" + capacity + "', trainer_id = '" + trainer.getTrainerId() + "', workshop_status_code = '" + statusCode.getStatusCode() + "' ";
		sql += "WHERE workshop_id = '" + workshopId + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public boolean updateCapacity(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "UPDATE ooadpj.workshop SET max_cap = '" + capacity + "' ";
		sql += "WHERE workshop_id = '" + workshopId + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		if(capacity <= 0){
			changeWorkshopStatusToFUL();
		}
		else if(!statusCode.getStatusCode().equals("AVA")){
			changeWorkshopStatusToAVA();
		}
		return success;
	}

	public boolean changeWorkshopStatusToAVA(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "UPDATE ooadpj.workshop SET workshop_status_code = 'AVA' ";
		sql += "WHERE workshop_id = '" + workshopId + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public boolean changeWorkshopStatusToFUL(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "UPDATE ooadpj.workshop SET workshop_status_code = 'FUL' ";
		sql += "WHERE workshop_id = '" + workshopId + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public static void changeWorkshopStatusToCLS(){
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "UPDATE ooadpj.workshop SET workshop_status_code = 'CLS' ";
		sql += "WHERE CAST(CURRENT_TIMESTAMP AS DATETIME) > start_date_time - INTERVAL '3' DAY";
		db.updateRequest(sql);
		db.terminate();
	}
	
	public static void changeWorkshopStatusToOBS(){
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "UPDATE ooadpj.workshop SET workshop_status_code = 'OBS' ";
		sql += "WHERE CAST(CURRENT_TIMESTAMP AS DATETIME) > end_date_time";
		db.updateRequest(sql);
		db.terminate();
		Workshop_BookWorkshopEntity.changeBookingStatus();
	}
	
	public boolean deleteWorkshop(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "DELETE FROM ooadpj.workshop WHERE workshop_id = '" + workshopId + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
}
